<?php

/**
 * Created by PhpStorm.
 * User: 86173
 * Date: 2020-06-13
 * Time: 17:22
 */
namespace libs;

use \mysqli;
class DB
{
    static protected $host = "127.0.0.1";
    static protected $userName = "root";
    static protected $password = "123";
    static protected $dbName = "redis_test";
    static protected $port = 3306;
    static protected $mysqli_obj;
    static protected $table_name;
    protected $where;
    protected $limit;
    protected $order_by;
    protected $group_by;
    protected $ors;
    protected $in;
    protected $filed;

    public function __construct($table_name)
    {
        self::$table_name = $table_name;
        self::connect_db();
    }

    /**
     * @param $table_name
     * @return DB|string
     * 操作的表名
     */
    static public function table($table_name)
    {
        return new self($table_name);
    }

    /**
     * @return string
     * 链接数据库
     */
    static protected function connect_db()
    {
        self::$mysqli_obj = @new Mysqli(self::$host,self::$userName,self::$password,self::$dbName,self::$port);
        if( self::$mysqli_obj->connect_errno){
            return json_encode(["code"=>2,"msg"=>"数据库链接失败，原因如下：".self::$mysqli_obj ->connect_error]);
        }
        $charset =  self::$mysqli_obj->set_charset("utf8");
        if(!$charset){
            return json_encode(["code"=>2,"msg"=>"字符集设置失败，原因如下：".self::$mysqli_obj ->error]);
        }
    }

    /**
     *  判断数据库是否链接成功
     */
    static protected function connect_exists()
    {
        if(!self::$mysqli_obj  instanceof Mysqli) self::connect_db();
    }

    /**
     * @param $key
     * @param $value
     * @param string $sign
     * @return $this
     * 查询条件     or
     */
     public function ors($key,$value,$sign='=')
    {
        if(!in_array($sign,['>','<',"=",">=","<=","!=","<>"])) exit("非法字符：{$sign}");
        if(!empty($this->where)){
            $this->ors =  "or `{$key}` {$sign} '{$value}'";
        }
        return $this;
    }

    /**
     * @param $key
     * @param $value
     * @param string $sign
     * @return DB
     * 查询条件     where
     */
     public function where($key,$value,$sign = '=')
    {
        if(!in_array($sign,['>','<',"=",">=","<=","!=","<>"])) exit("非法字符：{$sign}");
        if(empty($this->where)){
            $this->where = "where `{$key}` {$sign} '{$value}'";
        }else{
            $this->where = $this->where." and `{$key}` {$sign} '{$value}'";
        }
        return $this;
    }

    /**
     * @param $start
     * @param $num
     * @return DB
     * 限制查询数据的数量  limit
     */
     public function limit($start,$num)
    {
        $this->limit = " limit $start,$num";
        return $this;
    }

    /**
     * @param $field_name
     * @param string $sort
     * @return DB
     * 数据排序     order by
     */
     public function order_by($field_name,$sort='ASC')
    {
        if(!in_array($sort,["ASC","DESC","asc","desc"])) exit('排序规则错误：$sort='.$sort);
        if(empty($this->order_by)){
            $this->order_by = " order by {$field_name} {$sort}";
        }else{
            $this->order_by .= ",{$field_name} {$sort}";
        }
        return $this;
    }

    /**
     * @param $field_name
     * @return DB
     * 数据分组  group by
     */
     public function group_by($field_name)
    {
        $this->group_by = " group by `$field_name` ";
        return $this;
    }

    /**
     * @param $key
     * @param array $data
     * @return DB
     * 数据查询    in
     */
     public function in($key,array $data){
        $str =  "('" .implode("','",$data) . "')";
        if(empty($this->where)){
            $this->in = " where `{$key}` in $str";
        }else{
            $this->in = "and `{$key}` in $str";
        }
        return $this;
    }

    //查询想要的字段
    public function filed_name(array $filed_arr = [])
    {
        if(empty($filed_arr)){
            $this->filed = "*";
        }else{
            $this->filed = implode(",",$filed_arr);
        }
        return $this;
    }

    /**
     * 查询某表的数据
     * @param array $field
     * @return array
     */
     public function query()
    {
        self::connect_exists();
        $sql = sprintf("select %s from `%s`",$this->filed ?? "*",self::$table_name);
        $query_sql_arr = ["where","ors","group_by","in","order_by","limit"];
        foreach($query_sql_arr as $value){
            if(!empty($this->$value)){
                $sql.= $this->$value;
            }
        }
        return self::exe_sql($sql);
    }

    /**
     * @param $sql
     * @return array
     * 执行sql是否正确
     */
    static protected function exe_sql($sql){
        $sql_type = substr($sql,0,6);
        if($sql_type == "select"){
            $result = self::$mysqli_obj ->query($sql);
            if(self::$mysqli_obj ->errno){
                return [
                    "code" => 2,
                    "msg" => "数据查询失败，原因如下：".self::$mysqli_obj ->error,
                    "error_sql"=>$sql
                ];
            }else{
                $data = $result->fetch_all(MYSQLI_ASSOC);
                if(empty($data)) $data = ["code"=>2,"msg"=>"数据为空","sql"=>$sql];
                $result->free_result();
                return $data;
            }
        }
    }

    /**
     * @param array $data
     * @return array
     *  增加一条数据
     */
     public function insert(array $data,$info = "增加")
    {
        self::connect_exists();
        $sql = self::get_insert_sql($data);
        $result = self::$mysqli_obj->query($sql);
        return self::deal_result($result,$sql,$info);
    }

    //数据总数
    public function tableCount()
    {

    }
    /**
     * @param $table_name
     * @param $data
     * @return string
     * 获取增加sql
     */
    static protected function get_insert_sql($data)
    {
        $key_str = "";
        $value_str = "";
        foreach($data as $key => $value){
            $key_str .= "`{$key}`,";
            $value_str .= "'{$value}',";
        }
        $key_str = substr($key_str,0,-1);
        $value_str = substr($value_str,0,-1);
        return sprintf("insert into `%s`(%s) values(%s)",self::$table_name,$key_str,$value_str);
    }
    /**
     * @param array $data
     * @return array
     * 删除数据库数据
     */
     public function delete(array $data = [],$info = "删除")
    {
        self::connect_exists();
        if(empty($data)){
            return ["code"=>2,"msg"=>"缺少删除条件"];
        }
        $sql = self::get_delete_sql($data);
        $result = self::$mysqli_obj->query($sql);
        return self::deal_result($result,$sql,$info);
    }

    /**
     * @param $table_name
     * @param $data
     * @return string
     * 获取删除sql
     */
     protected function get_delete_sql($data)
    {
        $str = "";
        foreach($data as $key => $value){
            $str .= "`{$key}`='{$value}' and ";
        }
        $str = substr($str,0,-5);
        if(!empty($this->where)){
            $str = $this->where . ' and ' . $str;
        }else{
            $str = "where".$str;
        }
        return sprintf("delete from `%s` %s",self::$table_name,$str);
    }

    /**
     * @param $table_name
     * @param array $data
     * @param $where
     * @return array
     * 修改
     */
     public function update(array $data,$where,$info = "修改")
    {
        self::connect_exists();
        $sql = self::get_update_sql($data,$where);
        $result = self::$mysqli_obj->query($sql);
        return self::deal_result($result,$sql,$info);
    }

    /**
     * @param $table_name
     * @param $data
     * @param $where
     * @return string
     * 获取修改sql
     */
     protected function get_update_sql($data,$where)
    {
        if(empty($where)){
            exit('$where 参数不能为空');
        }
        $str =  "";
        $if = "";
        foreach($data as $key => $value){
            $str .= "`{$key}`='{$value}',";
        }
        foreach($where as $key => $value){
            $if .= "`{$key}`='{$value}' and ";
        }
        $str = substr($str,0,-1);
        $if = substr($if,0,-5);
        if(empty($this->where)){
            return sprintf("update `%s` set %s where %s",self::$table_name,$str,$if);
        }else{
            return sprintf("update `%s` set %s %s %s",self::$table_name,$str,$this->where."and",$if);
        }
    }

    /**
     *   处理  增加  删除 修改   的结果
     * @param $result
     * @param $sql
     * @param $deal
     * @return array
     */
    static protected function deal_result($result,$sql,$info)
    {
        $sql_type = substr($sql,0,6);
        $data = ["code" =>1,"msg" =>$info. "成功","sql" => $sql];
        if($sql_type == "insert"){
            $data['insert_id'] = self::$mysqli_obj->insert_id;
        }
        if($result && self::$mysqli_obj->affected_rows > 0){
            return $data;
        }else{
            return ["code" =>2,"msg" =>$info. "失败","sql" => $sql];
        }
    }

}
//$data = DB::table("student")->filed_name(["id","name"])->query();
    //$data = DB::table("student")->insert(["name"=>"ttttt","age"=>44,"sex"=>"男","create_time"=>date("Y-m-d H:i:s")]);
//$data = DB::table("student")->where("id",71)->update(["name"=>"唐伟"],["age"=>44]);
//  $data = DB::table("student")->where("name","唐伟")->delete(["id"=>73]);
//var_dump($data);